<?php
	define('CONFIG_FILE',true);
    include '../config.php';
    
	define('DB_FILE',true);
	require_once '../Tool/DB/SqlDBManager.class.php';
	
	session_start();
	if (empty($_SESSION["userType"])) {
		$url="../Index.php";
		include_once '../Tool/Common/Redirect.php';
		exit();
	}

	if($_SESSION["userType"]=="Therapy Assistant"){
        $url="../Index.php";
		include_once '../Tool/Common/Redirect.php';
		exit();
    }

	$sqlDBManager = new SqlDBManager();

	$page = isset($_POST['page']) ? intval($_POST['page']) : 1;
	$rows = isset($_POST['rows']) ? intval($_POST['rows']) : 10;
	$sort = isset($_POST['sort']) ? strval($_POST['sort']) : 'NRIC';
	$order = isset($_POST['order']) ? strval($_POST['order']) : 'asc';
	$searchid='';
	if(isset($_POST['searchid'])){
		$searchid = $_POST['searchid'];
	}
	$userType = $_SESSION["userType"];
	if(!empty($_SESSION["centreName"])){
		$centreName = $_SESSION["centreName"];
	}

	$offset = ($page - 1) * $rows;
	$result = array();

	if($userType=="System Admin"){
		$sql = "SELECT count(*) FROM attendancerecord a, client c, centre ce
			WHERE a.NRIC = c.NRIC AND DATE(SignInTimeStamp)=CURDATE() AND a.CentreId = ce.CentreId
			AND (a.NRIC LIKE  ?
			OR c.CustomerName LIKE  ?
			OR DATE(SignInTimeStamp) LIKE ?
			OR ce.CentreName LIKE ?
			OR TIME(SignInTimeStamp) LIKE  ?
			OR TIME(SignOuttimeStamp) LIKE  ?
			OR a.Status LIKE  ?)";

		//Filter the $searchid
		$searchid=addslashes($searchid);
		$searchid=str_replace("%", "\%", $searchid);
		$searchid=str_replace("_", "\_", $searchid);
		
		$parameters = array("%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%");		
		
		$res=$sqlDBManager->queryRow($sql,$parameters);
		
		if(!empty($res)){
			$result["total"] = $res[0];
		}
		else{
			$result["total"] = 0;
		}
		
		//Filter the $searchid
		$searchid=addslashes($searchid);
		$searchid=str_replace("%", "\%", $searchid);
		$searchid=str_replace("_", "\_", $searchid);
		
		$sql ="SELECT a.NRIC, c.CustomerName, ce.CentreName, DATE( SignInTimeStamp ) AS Date, TIME( SignInTimeStamp ) AS TimeIn, TIME( SignOutTimeStamp ) AS TimeOut, a.AttendanceRecordID, a.Status
			FROM attendancerecord a, client c, centre ce
			WHERE a.NRIC = c.NRIC AND DATE( SignInTimeStamp )=CURDATE() AND a.CentreId = ce.CentreId
			AND (a.NRIC LIKE ?
			OR c.CustomerName LIKE  ?
			OR DATE( SignInTimeStamp ) LIKE ?
			OR ce.CentreName LIKE ?
			OR TIME( SignInTimeStamp ) LIKE  ?
			OR TIME( SignOuttimeStamp ) LIKE ?
			OR a.Status LIKE  ?)
			order by $sort $order limit $offset,$rows";
						
		$parameters = array("%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%");
		$res=$sqlDBManager->queryRows($sql,$parameters);
		
		$items = array();
		if(!empty($res)){
			for($i=0;$i<count($res);$i++){
				array_push($items, $res[$i]);
			}	
		}	
		//close connection
	  	$sqlDBManager->close_connect();
		
		$result["rows"] = $items;
		echo json_encode($result);

	}else{
		$sql = "SELECT count(*) FROM attendancerecord a, client c, centre ce
			WHERE a.NRIC = c.NRIC AND DATE(SignInTimeStamp)=CURDATE() AND a.CentreId = ce.CentreId AND ce.CentreName=?
			AND (a.NRIC LIKE  ?
			OR c.CustomerName LIKE  ?
			OR DATE( SignInTimeStamp ) LIKE ?
			OR TIME( SignInTimeStamp ) LIKE  ?
			OR TIME( SignOuttimeStamp ) LIKE  ?
			OR a.Status LIKE ?)";

		//Filter the $searchid
		$searchid=addslashes($searchid);
		$searchid=str_replace("%", "\%", $searchid);
		$searchid=str_replace("_", "\_", $searchid);
		
		$parameters = array($centreName,"%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%");		
		
		$res=$sqlDBManager->queryRow($sql,$parameters);
		
		if(!empty($res)){
			$result["total"] = $res[0];
		}
		else{
			$result["total"] = 0;
		}
		
		//Filter the $searchid
		$searchid=addslashes($searchid);
		$searchid=str_replace("%", "\%", $searchid);
		$searchid=str_replace("_", "\_", $searchid);
		
		$sql ="SELECT a.NRIC, c.CustomerName, ce.CentreName, DATE( SignInTimeStamp ) AS Date, TIME( SignInTimeStamp ) AS TimeIn, TIME( SignOutTimeStamp ) AS TimeOut, a.AttendanceRecordID, a.Status
			FROM attendancerecord a, client c, centre ce
			WHERE a.NRIC = c.NRIC AND DATE( SignInTimeStamp )=CURDATE() AND a.CentreId = ce.CentreId AND ce.CentreName=?
			AND (a.NRIC LIKE ?
			OR c.CustomerName LIKE  ?
			OR DATE( SignInTimeStamp ) LIKE ?
			OR TIME( SignInTimeStamp ) LIKE  ?
			OR TIME( SignOuttimeStamp ) LIKE ?
			OR a.Status LIKE  ?)
			order by $sort $order limit $offset,$rows";
						
		$parameters = array($centreName,"%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%");		
		$res=$sqlDBManager->queryRows($sql,$parameters);
		
		$items = array();
		if(!empty($res)){
			for($i=0;$i<count($res);$i++){
				array_push($items, $res[$i]);
			}	
		}	
		//close connection
	  	$sqlDBManager->close_connect();
		
		$result["rows"] = $items;
		echo json_encode($result);	
	}

?>